﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.IO;
using FluentETL;
using FluentETL.Impl;

namespace FluentETL_Examples
{
    public static class Example_OleDb
    {
        public static void Run()
        {
            string dataDir = ConfigurationManager.AppSettings["DataDirectory"];
            string outputDB = Path.GetFullPath(Path.Combine(dataDir, "MyCompany_Access.mdb"));
            string outputConnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ outputDB + ";";

            var employeeList = from n in Enumerable.Range(1, 50)
                               let name = "Person " + n
                               let joinDate = new DateTime(2007, 1, 1).AddMonths(n)
                               select new
                               {
                                   EmployeeId = n,
                                   FirstName = name,
                                   LastName = name,
                                   Joined = joinDate,
                                   Salary = 50000,
                                   Resigned = joinDate.AddMonths(24) // Resigned after 2 years
                               };

            // Load initial data
            // -------------------------------------
            ISource src = new ObjectSource().From(employeeList);
            IDestination dest = new OleDbDestination()
                                .Connection(outputConnStr)
                                .ToTable("Employees")
                                // You can run any custom SQL prior to the transform using PreTransform()
                                // See also PostTransform()
                                .PreTransform(conn =>
                                {
                                    var cmd = conn.CreateCommand();
                                    cmd.CommandText = "DELETE FROM Employees";
                                    cmd.ExecuteNonQuery();
                                });
            Transform t = new Transform(src, dest)
                                .Map("EmployeeId")
                                .Map("FirstName")
                                .Map("LastName")
                                .Map("Joined")
                                .Map("Salary")
                                .Execute();

            Console.WriteLine("List of employees loaded. Take a look at Employees table in the .MDB file, you will see names and salaries");
            Console.WriteLine("-- Press key to continue --");
            Console.ReadKey();

            // Clear salary, remove resigned employees
            // -------------------------------------
            ISource src2 = new ObjectSource().From(employeeList);

            IDestination dest2 = new OleDbDestination()
                                .Connection(outputConnStr)
                                .ToTable("Employees");

            Transform t2 = new Transform(src2, dest2)
                                .Map("EmployeeId")
                                .Map("Salary", "Salary", salary => null)
                                .DetermineOperation(row =>
                                {
                                    if (((DateTime)row["Resigned"]) <= new DateTime(2010, 8, 1))
                                        return RowOperation.Delete;
                                    else
                                        return RowOperation.Update;
                                })
                                .OnRowAfterProcess((row, op) =>
                                {
                                    Console.WriteLine(op.ToString() + " : Employee ID = " + row["EmployeeId"]); 
                                })
                                .Execute();


        }
    }
}
